Copyright: Copyright © 1992-1995 Blue Lagoon Software, Inc.
Citation: Copyright © 1992-1995 Blue Lagoon Software, Inc.


Contents
SQL Inspector for Windows

Version 2.0

Copyright © 1992-95 Blue Lagoon Software, Inc.

Blue Lagoon Software, Inc.

6047 Tampa Ave. Suite 209

Tarzana, CA  91356 USA

Tel.	(818) 345-2200

Fax	(818) 345-8905

BBS	(818) 343-8433

CompuServe 70152,1601

Internet blagoon@netcom.com

Contents

Disclaimer

Introduction

Menu Commands

Options Dialog Box

Network Multi-Workstation Tracing

Viewing Multi-Workstation Log Files

DB-Library Operations

SQL_Inspector's Traced Data


Disclaimer

SQL Inspector for Windows, Copyright © 1992-1995 by Blue Lagoon Software Inc., is provided as-is without warranty of any kind.  Blue Lagoon Software Inc. shall not be liable for any loss of profits, loss of use, interruption of business, nor indirect, special, incidental or consequential damages of any kind from the use of this product.


Introduction

Thank you for purchasing SQL Inspector for Windows!

DB-Library provides applications under Windows 3.1, whether written in Microsoft Visual Basic, Microsoft Access, Power Builder, C, or Gupta SQLWindows, with connectivity to Microsoft/Sybase SQL Server.  SQL Inspector for Windows does performance monitoring and tracing against DB-Library.

The version of SQL Inspector for Windows you have purchased is to be used under Windows 3.1 and against Microsoft/Sybase SQL Server 4.2 and Sybase SQL Server System 10.  The Inspector allows you to trace most DB-Library operations simultaneously.

To make the most out of SQL Inspector for Windows, you should have a copy of The Microsoft SQL Server Programmer's Reference handy.  This valuable reference lists all of the DB-Library operations along with the parameters passed and return values.  This information will be needed when examining SQL Inspector's traced data.


Menu commands

SQL Inspector for Windows has five menus: Inspector, Marker, Search, Window and Help.  Accelerator keystrokes are provided to access many of the menu commands; these are listed in the table below:

Keystroke	Menu Command

Ctrl+I	Inspector On/Off

Ctrl+O	Options dialog

Ctrl+F	Find text

Ctrl+R	Find First

Ctrl+P	Find Previous

Ctrl+N	Find Next

Ctrl+L	Find Last

Ctrl+W	Clear Window

Inspector Menu Commands

Inspector On/Off

Toggles trace mode.  When SQL Inspector is tracing, the caption bar of the application will be set to "<< SQL Inspector >>".  When SQL Inspector is not tracing, the caption bar of the application will be set to "SQL Inspector".

Options

Brings up the SQL Inspector Options dialog box.  It is within this dialog box that tracing options are setup.

Exit

Exits SQL Inspector.

Marker! menu command

Brings up the Marker dialog box.  The Marker dialog box is designed to allow user entry of a string of up to 255 characters which will be inserted into the trace log.

Find menu commands

Find Text...

Brings up the Find dialog box.  This is a "floating" dialog box used to search for text within the Inspector's output window.

In the Find what box, enter the text you wish to search for.  Check the Match Case box by clicking on it if you want to make the search case-sensitive.  Then, click on the Find First button to find the first occurrence of the text; you can also search for the next, previous or last occurrence by clicking on the appropriate button.

The Find dialog box remembers the last five text strings you have searched for in the current SQL Inspector session; you can select one of these by clicking on the down-arrow next to the Find what box.

You can switch back to the Inspector window by clicking on it, and the Find window will remain open.

Find First

Find Previous

Find Next

Find Last

These menu commands find the appropriate occurrence of the last text string specified in the Find dialog box.

Window menu commands

Clear Window

Clears the SQL Inspector window.  Any output in the SQL Inspector window will be cleared.

Help menu commands

Contents

Brings up the contents portion of the help file.

About

Displays application about box.


Options dialog box

This dialog box is accessed by choosing the Options... selection from the Inspector menu.

Display mode combo box

The Display mode combo box contains a list of the different trace modes available.  The SQL Inspector currently supports Extended, Query and Script modes.  These modes are described in the SQL Inspector's Traced Data section.

Programs combo box

The Programs combo box contains a list of all programs currently running under Windows.  To trace a specific applications’ DB-Library accesses, first make sure that the application is currently running, then select it from this combo box.  SQL Inspector will only display DB-Library operations made by this application.  The "(All)" option is designed to display DB-Library operations made by ALL applications.

Operations list box

The Operations list box is a multi-selection list box.  You can select some or all of the listed operations to be displayed by the Inspector when they occur.  This allows you to filter out the operations you're not interested in tracing.  Note that this list box is not available in Query or Script display modes; these modes automatically select which operations to monitor in order to produce their output.

Select all push button

Selects ALL operations in the Operations list box.

Deselect all push button

Deselects ALL operations in the Operations list box.

Window check box

This check box should be selected to indicate that you want SQL Inspector to display what it traces in the application area of the SQL Inspector’s application window.

Workstation check box

This check box should be selected to indicate that you want SQL Inspector to include the workstation name on each line of the trace output.  The workstation name is set by entering a 16 character string in the entry field adjacent to this check box.

Time stamp check box

This check box should be selected to indicate that you want SQL Inspector to include the time stamp on each line of the trace output.

File check box

This check box should be selected to indicate that you want SQL Inspector to create a log file which will contain all traced data.  As SQL Inspector traces, it will append its data in ASCII format to a log file whose path name is specified in the adjacent entry field.

Append file check box

This check box should be selected to indicate that you want SQL Inspector to open the trace log file in append mode.  If this check box is not selected and the file check box is selected, then each time the SQL Inspector is started, the trace log file will be truncated to zero length.

OK push button

Accepts the changes made.

Cancel push button

Cancels the changes made.


Network Multi-Workstatioon Tracing
Network Multi-Workstation Tracing

The SQL Inspector’s log file tracing mechanism uses network file sharing.  This means that by enabling the File and Append File features in the Options dialog box, all traced output will be appended to the trace log file using network file sharing.  Thus, when more than one user has this feature enabled and the same network trace log file is used, then DB-Library operations for these users will be written into the same trace log file in the order in which they occur.

Naturally, if several users are sharing a network trace log file, the workstation name feature becomes very useful in identifying which DB-Library operations occurred on a particular workstation.  In addition, the time stamp is also very useful to help understand when an operation occurred.

By only tracing DB-Library operations that are necessary, better performance will be available for the application that is being traced.  In addition, with several users tracing to the same network trace log file, choosing DB-Library operations becomes all the more important since each workstation running the Inspector will use file locking to prevent access to the file while it is writing its trace output.

See Also

Viewing Multi-Workstation Log Files


DB-Library Operations

The following is a list of some commonly used DB-Library operations which are traceable in SQL Inspector's Extended display mode.

dbbind

"Binds," or associates, a data buffer with a column in a result set.  Subsequent dbgetrow and dbnextrow calls update the buffer with the data for the specified column in the newly-fetched row.  SQL Inspector displays the returned data stored in a bound column buffer after every dbgetrow and dbnextrow call that affects the buffer.

dbclose

Indicates that a DB-Library connection has been closed.

dbcmd

Prepares an SQL string for execution. The SQL query text is passed as a parameter to this operation.  The execution time for this operation only indicates the amount of time to prepare the SQL string for execution.  The actual execution of this query takes place when the next dbsqlexec operation occurs.

dbdata

Retrieves the result data from a previous dbsqlexec.  This is one way for applications to retrieve result data (using dbbind is the other way).  SQL Inspector displays the returned data in the format appropriate for the data type.  Note:  this operation may occur frequently, depending on the application being traced.  Thus, tracing this operation may slow the performance of your system, and if network tracing is being performed, it may slow the performance of other systems.

dbgetrow, dbnextrow

These two functions retrieve a row from the current result set.  SQL Inspector displays any bound column data that is updated by either of these operations.  Note: these operations may occur frequently.  Tracing these operations, especially to a multi-workstation log file, can noticeably degrade workstation performance.

dbopen

Opens a DB-Library connection to SQL Server.  The name of the server is passed as a parameter to this operation.

DBSETLNAME

Displays connection information about a DB-Library connection such as user name, password, workstation name, language, etc.  Note that the SQL Inspector normally does not show password strings in its output, in the interest of preserving security.  If you need to see the password information, please contact Blue Lagoon Software.

dbsqlexec

Executes a SQL query which was prepared by dbcmd.  The total execution time of this operation plus the total execution time of the prior dbcmd operations indicates the total amount of execution time for this query.


Viewing Multi-Workstation Log Files

SQL Inspector includes a utility, Multi-Workstation Log Viewer, which allows a network administrator to view a shared log file.  This utility can be started by selecting the Launch Log File Viewer item from the Inspector menu, or by double-clicking the Log Viewer's icon in the Program Manager.

The Viewer normally displays the end of the log file, and continually scrolls new data into view as it is written to the log file by any workstations.

Choosing the Tail Off! command from the menu causes the Viewer not to automatically show new data; this lets the user browse through the file without being interrupted by new data.

The Viewer normally brings up the log file as configured in the SQL Inspector.  However, the user may open a different file by choosing the Open... command from the File menu.

The Find menu commands bring up a dialog box that lets the user search the log file for a given text string.  As in the Inspector, the user can perform Find First, Find Previous, Find Next and Find Last operations to locate a specific occurrence of the search text.

The Find window remains active until it is closed, to allow repeating of searches.


SQL Inspector's Traced Data

It is possible to have SQL Inspector trace data into both SQL Inspector's application window and/or a log file in ASCII format.  The application window can hold approximately 3000 lines of Extended Mode output.  The capacity of the log file is limited only by available disk space.

SQL Inspector has three output modes: Extended, Query and Script.  These are described in the following sections.

Extended Mode

When a DB-Library operation occurs, SQL Inspector reports the execution time of the call in seconds:milliseconds, the application that executed the operation, the name of the operation and the parameters and return value.  This information is displayed in the application window and/or saved to the log file in the following format:

000:016 SQLADMIN: dbcmd(9332, "select * from foo"), RETVAL=SUCCESS

The output of each operation is placed on a single line by itself.  Each output line contains the following information:

•	Timestamp when the API call began (if timestamps are enabled)

•	Workstation name (if workstation names are enabled)

•	Execution time of the call, in seconds:milliseconds

•	Application name

•	DB-Library function name, parameters and return value

For example, it took 16 milliseconds to execute the above DB-Library operation.  This performance information is especially useful for commands such as dbsqlsend, dbcmd, dbsqlok and dbsqlexec because they send SQL queries to database servers for execution.  By measuring their performance, it is possible to determine how long queries and stored procedures take to execute.

Referring to the Microsoft SQL Server Programmer's Reference, we can see that dbcmd has two parameters: a handle to the database connection, and a long pointer to the SQL command to execute.  The return value is an integer.  As we can see, the dbcmd call was made by SQLADMIN, the database handle is 9332, and the command being executed is "select * from foo".  The return value is SUCCESS and according to the SQL Server documentation, this means that the operation was successful.

Options exist within SQL Inspector to allow the time stamp and workstation name to be included in each trace output line.  The time stamp is very useful because it allows you to see what time a particular DB-Library operation took place.  The workstation name is also very useful when the trace log file is located on a network drive and is shared by many different users.

Query Mode

When in Query Mode, the SQL Inspector only outputs trace data for server connect and disconnect operations, and query execution operations.  This information is output in the following format:

-----------------------------------------------------------

SUSAN 000:019 ISQLW

use pubs

*** SUCCEED ***

-----------------------------------------------------------

SUSAN 000:003 ISQLW

select Name, Account, Balance from TestTable

*** SUCCEED ***

-----------------------------------------------------------

Script Mode

When the SQL Inspector is configured for Script mode, it outputs raw queries as passed to the DB-Library dbcmd function.  After each raw query, the Inspector adds the delimiter specified in the Options dialog box; for example, it could be configured to add the word "go" as a delimiter after each query.

Note that the Delimiter box only appears in the Options dialog when you have selected Script mode; it does not apply to other output modes.

Retrieved Data Output Formats

Several DB-Library functions return variable-format data, which the SQL Inspector displays in its Extended Mode output.  Where possible, the Inspector identifies the correct data type and displays the data in that format.  In cases where the format cannot be identified, the Inspector will show the data in hexadecimal format (i.e. a hex dump).

Below is a listing of the data types recognized by the Inspector, along with the output format of each.

Data Type	Output Format

SQLARRAY 	Character string

SQLBINARY	Hex dump

SQLBIT	True/False

SQLCHAR	Character string

SQLDATETIME	MM/DD/YY HH:MM:SS

SQLDATETIM4	MM/DD/YY HH:MM:SS

SQLDATETIMN	MM/DD/YY HH:MM:SS

SQLFLT4	Numeric decimal

SQLFLT8	Numeric decimal

SQLFLTN	Numeric decimal

SQLIMAGE	Hex dump

SQLINT1	Numeric integer

SQLINT2	Numeric integer

SQLINT4	Numeric integer

SQLINTN	Numeric integer

SQLMONEY	Numeric decimal

SQLMONEY4	Numeric decimal

SQLMONEYN	Numeric decimal

SQLTEXT	Character string

SQLVARBINARY	Hex dump

SQLVARCHAR	Character string


Marker Dialog Box

This dialog box lets you enter a string of up to 255 characters to be written into the Inspector's window and log file output; this feature is useful for inserting annotations into the traced output.


Find Dialog Box

Selecting the Find text... menu command Brings up the Find dialog box.  This is a "floating" dialog box used to search for text within the Inspector's output window.

In the Find what box, enter the text you wish to search for.  Check the Match Case box by clicking on it if you want to make the search case-sensitive.  Then, click on the Find First button to find the first occurrence of the text; you can also search for the next, previous or last occurrence by clicking on the appropriate button.

The Find dialog box remembers the last five text strings you have searched for in the current SQL Inspector session; you can select one of these by clicking on the down-arrow next to the Find what box.

You can switch back to the Inspector window by clicking on it, and the Find window will remain open.

The Find First, Find Previous, Find Next and Find Last menu commands find the appropriate occurrence of the last text string specified in the Find dialog box.